Create Database KillBillDB
Go

Use KillBillDB
Go

Create table [Person]
(
	[PersonID] Char(10) NOT NULL,
	[RUC] Char(13) NULL,
	[FirstName] Varchar(20) NOT NULL,
	[LastName] Varchar(20) NOT NULL,
	[Email] Varchar(40) NOT NULL,
	[Address] Varchar(100) NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PersonID])
) 
go

Create table [PersonPhone]
(
	[PersonID] Char(10) NOT NULL,
	[PhoneNumber] Char(10) NOT NULL,
Primary Key ([PersonID],[PhoneNumber])
) 
go

Create table [Customer]
(
	[CustomerID] Integer NOT NULL,
	[PersonID] Char(10) NOT NULL,
	[Reference] Text NULL,
	[CustomerStatusID] Varchar(20) NOT NULL,
Primary Key ([CustomerID])
) 
go

Create table [CustomerStatus]
(
	[CustomerStatusID] Varchar(20) NOT NULL,
Primary Key ([CustomerStatusID])
) 
go

Create table [Contact]
(
	[ContactID] Char(10) NOT NULL,
	[ProviderID] Integer NOT NULL,
	[ContactPositionID] Varchar(20) NOT NULL,
Primary Key ([ContactID])
) 
go

Create table [ContactPosition]
(
	[ContactPositionID] Varchar(20) NOT NULL,
Primary Key ([ContactPositionID])
) 
go

Create table [Employee]
(
	[EmployeeID] Integer Identity NOT NULL,
	[PersonID] Char(10) NOT NULL,
	[Password] Text NOT NULL,
	[EmployeeStatusID] Varchar(20) NOT NULL,
Primary Key ([EmployeeID])
) 
go

Create table [EmployeeStatus]
(
	[EmployeeStatusID] Varchar(20) NOT NULL,
Primary Key ([EmployeeStatusID])
) 
go

Create table [EmployeeRole]
(
	[EmployeeRoleID] Varchar(40) NOT NULL,
Primary Key ([EmployeeRoleID])
) 
go

Create table [EmployeeActivity]
(
	[EmployeeID] Integer NOT NULL,
	[EmployeeRoleID] Varchar(40) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([EmployeeID],[EmployeeRoleID])
) 
go

Create table [Provider]
(
	[ProviderID] Integer Identity NOT NULL,
	[Name] Varchar(20) NOT NULL,
	[ProviderStatusID] Varchar(20) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([ProviderID])
) 
go

Create table [ProviderStatus]
(
	[ProviderStatusID] Varchar(20) NOT NULL,
Primary Key ([ProviderStatusID])
) 
go

Create table [ProviderOffice]
(
	[ProviderID] Integer NOT NULL,
	[Address] Varchar(100) NOT NULL,
	[Phone] Char(10) NOT NULL,
	[isMain] Bit NOT NULL,
Primary Key ([ProviderID],[Address])
) 
go

Create table [PurchaseHeader]
(
	[PurchaseHeaderID] Integer Identity NOT NULL,
	[ProviderID] Integer NOT NULL,
	[SubTotal] Money NOT NULL,
	[TaxAmount] Decimal(10,2) NOT NULL,
	[TotalDue] Money NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PurchaseHeaderID])
) 
go

Create table [Payment]
(
	[PurchaseHeaderID] Integer NOT NULL,
	[PayDate] Datetime NOT NULL,
	[Value] Money NOT NULL,
Primary Key ([PurchaseHeaderID],[PayDate])
) 
go

Create table [Product]
(
	[ProductID] Integer NOT NULL,
	[Name] Varchar(40) NOT NULL, UNIQUE ([Name]),
	[Description] Text NOT NULL,
	[LastPrice] Money NOT NULL,
	[StockMin] Integer NOT NULL,
	[ProductStatusID] Varchar(20) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([ProductID])
) 
go

Create table [ProductStatus]
(
	[ProductStatusID] Varchar(20) NOT NULL,
Primary Key ([ProductStatusID])
) 
go

Create table [PurchaseDetail]
(
	[PurchaseHeaderID] Integer NOT NULL,
	[ProductID] Integer NOT NULL,
	[OrderDate] Datetime NOT NULL,
	[UnitPrice] Money NOT NULL,
	[OrderQty] Integer NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([PurchaseHeaderID],[ProductID])
) 
go

Create table [SalesHeader]
(
	[SalesHeaderID] Integer NOT NULL,
	[EmployeeID] Integer NOT NULL,
	[SubTotal] Money NOT NULL,
	[TaxAmount] Decimal(10,2) NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
	[CustomerID] Integer NOT NULL,
Primary Key ([SalesHeaderID])
) 
go

Create table [SalesDetail]
(
	[SalesHeaderID] Integer NOT NULL,
	[ProductID] Integer NOT NULL,
	[OrderDate] Datetime NOT NULL,
	[UnitPrice] Money NOT NULL,
	[OrderQty] Integer NOT NULL,
	[ModifiedDate] Datetime NOT NULL,
Primary Key ([SalesHeaderID],[ProductID])
) 
go


Alter table [PersonPhone] add  foreign key([PersonID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [Contact] add  foreign key([ContactID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [Employee] add  foreign key([PersonID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [Customer] add  foreign key([PersonID]) references [Person] ([PersonID])  on update no action on delete no action 
go
Alter table [SalesHeader] add  foreign key([CustomerID]) references [Customer] ([CustomerID])  on update no action on delete no action 
go
Alter table [Customer] add  foreign key([CustomerStatusID]) references [CustomerStatus] ([CustomerStatusID])  on update no action on delete no action 
go
Alter table [Contact] add  foreign key([ContactPositionID]) references [ContactPosition] ([ContactPositionID])  on update no action on delete no action 
go
Alter table [EmployeeActivity] add  foreign key([EmployeeID]) references [Employee] ([EmployeeID])  on update no action on delete no action 
go
Alter table [SalesHeader] add  foreign key([EmployeeID]) references [Employee] ([EmployeeID])  on update no action on delete no action 
go
Alter table [Employee] add  foreign key([EmployeeStatusID]) references [EmployeeStatus] ([EmployeeStatusID])  on update no action on delete no action 
go
Alter table [EmployeeActivity] add  foreign key([EmployeeRoleID]) references [EmployeeRole] ([EmployeeRoleID])  on update no action on delete no action 
go
Alter table [ProviderOffice] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update no action on delete no action 
go
Alter table [Contact] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update no action on delete no action 
go
Alter table [PurchaseHeader] add  foreign key([ProviderID]) references [Provider] ([ProviderID])  on update no action on delete no action 
go
Alter table [Provider] add  foreign key([ProviderStatusID]) references [ProviderStatus] ([ProviderStatusID])  on update no action on delete no action 
go
Alter table [Payment] add  foreign key([PurchaseHeaderID]) references [PurchaseHeader] ([PurchaseHeaderID])  on update no action on delete no action 
go
Alter table [PurchaseDetail] add  foreign key([PurchaseHeaderID]) references [PurchaseHeader] ([PurchaseHeaderID])  on update no action on delete no action 
go
Alter table [PurchaseDetail] add  foreign key([ProductID]) references [Product] ([ProductID])  on update no action on delete no action 
go
Alter table [SalesDetail] add  foreign key([ProductID]) references [Product] ([ProductID])  on update no action on delete no action 
go
Alter table [Product] add  foreign key([ProductStatusID]) references [ProductStatus] ([ProductStatusID])  on update no action on delete no action 
go
Alter table [SalesDetail] add  foreign key([SalesHeaderID]) references [SalesHeader] ([SalesHeaderID])  on update no action on delete no action 
go


Set quoted_identifier on
go


Set quoted_identifier off
go